package org.ccu.eshop.dao;

import org.ccu.eshop.domain.dto.MerchandiseVo;
import org.ccu.eshop.domain.dto.OrdersVo;
import org.ccu.eshop.utils.ComboPooledDataSourceUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrdersDao {
    /**
     * 根据订单编号查询订单信息
     * @param orderNo
     * @return
     */
    public OrdersVo selectOrdersByOrderNo(String orderNo) {
        OrdersVo ordersVo=null;
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{

                //2-1获得承接sql的容器对象
            String sql="SELECT orders.id,orders.member,cart,orderNO,DATE_FORMAT(orderDate,'%Y-%m-%d %H:%i:%S') orderDate,orderStatus,money from orders,cart where orders.cart=cart.id and orders.orderNO=?" ;
            //2-2获得sql语句
            PreparedStatement ps=connection.prepareStatement(sql);
            //2-3执行sql语句
            ps.setString(1,orderNo);
            ResultSet set=ps.executeQuery();
            if(set.next()){
                ordersVo=new OrdersVo( set.getInt("id"),set.getInt("member"),set.getInt("cart"),set.getString("orderNO"),set.getString("orderDate"),set.getInt("orderStatus"),set.getBigDecimal("money"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    return ordersVo;
    }

    /**
     *根据会员id查询订单列表 按时间降序
     * @param member
     * @return
     */
    public List<OrdersVo> selectOrdersListByMid(Integer member) {
        List<OrdersVo> ordersVoList=new ArrayList<>();

        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{

            //2-1获得承接sql的容器对象
            String sql="SELECT orders.id,orders.member,cart,orderNO,DATE_FORMAT(orderDate,'%Y-%m-%d') orderDate,orderStatus,money from orders,cart where orders.cart=cart.id and orders.member=? " +
                    " ORDER BY orderDate desc";
            //2-2获得sql语句
            PreparedStatement ps=connection.prepareStatement(sql);
            //2-3执行sql语句
            ps.setInt(1,member);
            ResultSet set=ps.executeQuery();
            while(set.next()){
              OrdersVo  ordersVo=new OrdersVo( set.getInt("id"),set.getInt("member"),set.getInt("cart"),set.getString("orderNO"),set.getString("orderDate"),set.getInt("orderStatus"),set.getBigDecimal("money"));
                ordersVoList.add(ordersVo);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ordersVoList;
    }

    /**
     * 订单id删除订单
     * @param id
     * @return
     */
    public boolean deleteOrderById(int id) {
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{
            //2-1获得承接sql的容器对象
            String sql="delete  from orders where id=?" ;
            //2-2获得sql语句
            PreparedStatement ps=connection.prepareStatement(sql);
            //2-3执行sql语句
            ps.setInt(1,id);
            int row=ps.executeUpdate();
            if(row>0){
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * 根据订单id查询订单
     * @param id
     * @return
     */
    public OrdersVo selectOrdersById(int id) {
        OrdersVo ordersVo=null;
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{

            //2-1获得承接sql的容器对象
            String sql="SELECT orders.id,orders.member,cart,orderNO,DATE_FORMAT(orderDate,'%Y-%m-%d %H:%i:%S') orderDate,orderStatus,money from orders,cart where orders.cart=cart.id and orders.id=? " ;
            //2-2获得sql语句
            PreparedStatement ps=connection.prepareStatement(sql);
            //2-3执行sql语句
            ps.setInt(1,id);
            ResultSet set=ps.executeQuery();
            if(set.next()){
                ordersVo=new OrdersVo( set.getInt("id"),set.getInt("member"),set.getInt("cart"),set.getString("orderNO"),set.getString("orderDate"),set.getInt("orderStatus"),set.getBigDecimal("money"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ordersVo;
    }
}
